# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：get_stock_rzrq.py
#日期：2019-03-15
#备注：获取东方财富网的每天统计的融资融券交易总信息存入到数据库，rzrqInfo_to_mysql()  
        http://dcfm.eastmoney.com//EM_MutiSvcExpandInterface/api/js/get?token=70f12f2f4f091e459a279469fe49eca5&st=tdate&sr=-1&p=1&ps=100&type=RZRQ_HSTOTAL_NJ&filter=(market=%27SZ%27),        
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import requests
import time
from pacong.stock.stock_config import db,cursor,headers
from pycacho.cachobase.logger import Logger

logger = Logger("getStockRzrq").get_log()


def createTable():
    sql1 = 'drop table if exists stock_rzrqInfo,stock_rzrqDetail;'
    sql2 = 'create table stock_rzrqInfo(rzrq_market varchar(20),rzrq_date varchar(20),close_price float,priceChg1 float,priceChg3 float,priceChg5 float,priceChg10 float,' \
           'rz_balance float,rz_rate float,rz_buy1 float,rz_repay1 float,rz_netBuy1 float,rz_buy3 float,rz_repay3 float,rz_netBuy3 float,rz_buy5 float,rz_repay5 float,rz_netBuy5 float,rz_buy10 float,rz_repay10 float,rz_netBuy10 float,' \
           'rq_balance float, rq_number float,rq_sell1 float, rq_repay1 float, rq_netSell1 float,rq_sell3 float, rq_repay3 float, rq_netSell3 float,rq_sell5 float, rq_repay5 float, rq_netSell5 float, rq_sell10 float, rq_repay10 float, rq_netSell10 float,' \
           'rzrq_balance float,rzrq_gap float,PRIMARY KEY (rzrq_market,rzrq_date));'
    sql3 = 'create table stock_rzrqDetail (rzrq_market varchar(20),rzrq_date varchar(20),rzrq_scode varchar(20),rzrq_sname varchar(20),price_close float,price_chg float,rz_rzye float,rz_rzyezb float,rz_rzmre float,rz_rzche float,rz_rzjmre float,' \
           'rq_rqye float,rq_rqyl float,rq_rqmcl float,rq_rqchl float,rq_rqjmcl float,rzrq_rzrqye float,rzrq_rzrqyecz float,' \
           'PRIMARY KEY (rzrq_scode,rzrq_date));'

    cursor.execute(sql1)
    cursor.execute(sql2)
    cursor.execute(sql3)
    db.commit()


def get_rzrqInfo(page,rzrq_type):
    url = 'http://dcfm.eastmoney.com//EM_MutiSvcExpandInterface/api/js/get?token=70f12f2f4f091e459a279469fe49eca5&' \
          'st=tdate&sr=-1&p={0}&ps=50&type=RZRQ_HSTOTAL_NJ&filter=(market=%27{1}%27)'.format(page,rzrq_type)
          # r=-1表示倒序,p=n表示第几页，ps50表示每页50条数据
    resp = requests.get(url, headers=headers)
    data = resp.text
    dict_data = eval(data.replace('[','').replace(']',''))
    for data in dict_data:
        # print(data)
        rzrq_market = rzrq_type
        rzrq_date = data ["tdate"][0:10]
        close_price = round(float(data ["close"]),2)       # 收盘价格
        priceChg1 = round(float(data ["zdf"]),2)           # 1日涨跌幅
        priceChg3 = round(float(data["zdf3"]),2)           # 3日涨跌幅
        priceChg5 = round(float(data["zdf5"]),2)           # 5日涨跌幅
        priceChg10 = round(float(data["zdf10"]),2)         # 10日涨跌幅
        rz_balance = float(data["rzye"])                   # 融资余额(元)
        rz_rate = round(float(data["rzyezb"]),2)           # 融资余额占流通市值比
        rz_buy1 = float(data["rzmre"])                     # 融资1日买入(元)
        rz_repay1 = float(data["rzche"])                   # 融资1日偿还(元)
        rz_netBuy1 = rz_buy1-rz_repay1                     # 融资1日净买入(元)
        rz_buy3 = float(data["rzmre3"])                    # 融资3日买入(元)
        rz_repay3 = float(data["rzche3"])                  # 融资3日偿还(元)
        rz_netBuy3 = rz_buy3-rz_repay3                     # 融资3日净买入(元)
        rz_buy5 = float(data["rzmre5"])                    # 融资5日买入(元)
        rz_repay5 = float(data["rzche5"])                  # 融资5日偿还(元)
        rz_netBuy5 = rz_buy5 - rz_repay5                   # 融资5日净买入(元)
        rz_buy10 = float(data["rzmre10"])                  # 融资10日买入(元)
        rz_repay10 = float(data["rzche10"])                # 融资10日偿还(元)
        rz_netBuy10 = rz_buy10 - rz_repay10                # 融资10日净买入(元)

        rq_balance = float(data["rqye"])                   # 融券余额(元)
        rq_number = float(data["rqyl"])                    # 融券余数（股）
        rq_sell1 = float(data["rqmcl"])                    # 融券1日卖出（股）
        rq_repay1 = float(data["rqchl"])                   # 融券1日偿还（股）
        rq_netSell1 = rq_sell1 - rq_repay1                 # 融券1日净卖出（股）
        rq_sell3 = float(data["rqmcl3"])                   # 融券3日卖出（股）
        rq_repay3 = float(data["rqchl3"])                  # 融券3日偿还（股）
        rq_netSell3 = rq_sell3 - rq_repay3                 # 融券3日净卖出（股）
        rq_sell5 = float(data["rqmcl5"])                   # 融券5日卖出（股）
        rq_repay5 = float(data["rqchl5"])                  # 融券5日偿还（股）
        rq_netSell5 = rq_sell5 - rq_repay5                 # 融券5日净卖出（股）
        rq_sell10 = float(data["rqmcl10"])                 # 融券10日卖出（股）
        rq_repay10 = float(data["rqchl10"])                # 融券10日偿还（股）
        rq_netSell10 = rq_sell10 - rq_repay10              # 融券10日净卖出（股）

        rzrq_balance = rz_balance + rq_balance             # 融资融券总余额（元）
        rzrq_gap = rz_balance - rq_balance                 # 融资融券差额（元）

        rzrqInfo_to_mysql(rzrq_market,rzrq_date,close_price,priceChg1,priceChg3,priceChg5,priceChg10,rz_balance,rz_rate,rz_buy1,rz_repay1,rz_netBuy1,
              rz_buy3,rz_repay3,rz_netBuy3,rz_buy5,rz_repay5,rz_netBuy5,rz_buy10,rz_repay10,rz_netBuy10,
              rq_balance, rq_number,rq_sell1, rq_repay1, rq_netSell1,rq_sell3, rq_repay3, rq_netSell3,
              rq_sell5, rq_repay5, rq_netSell5, rq_sell10, rq_repay10, rq_netSell10,rzrq_balance,rzrq_gap)


# 将每天融资融券信息插入到数据库
def rzrqInfo_to_mysql(rzrq_market,rzrq_date,close_price,priceChg1,priceChg3,priceChg5,priceChg10,rz_balance,rz_rate,rz_buy1,rz_repay1,rz_netBuy1,
              rz_buy3,rz_repay3,rz_netBuy3,rz_buy5,rz_repay5,rz_netBuy5,rz_buy10,rz_repay10,rz_netBuy10,
              rq_balance, rq_number,rq_sell1, rq_repay1, rq_netSell1,rq_sell3, rq_repay3, rq_netSell3,
              rq_sell5, rq_repay5, rq_netSell5, rq_sell10, rq_repay10, rq_netSell10,rzrq_balance,rzrq_gap):
    sql = "insert into stock_rzrqInfo (rzrq_market,rzrq_date,close_price,priceChg1,priceChg3,priceChg5,priceChg10,rz_balance,rz_rate,rz_buy1,rz_repay1,rz_netBuy1,rz_buy3,rz_repay3,rz_netBuy3,rz_buy5,rz_repay5,rz_netBuy5,rz_buy10,rz_repay10,rz_netBuy10," \
          "rq_balance, rq_number,rq_sell1, rq_repay1, rq_netSell1,rq_sell3, rq_repay3, rq_netSell3,rq_sell5, rq_repay5, rq_netSell5, rq_sell10, rq_repay10, rq_netSell10,rzrq_balance,rzrq_gap) values" \
          "('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');" \
          % (rzrq_market,rzrq_date,close_price,priceChg1,priceChg3,priceChg5,priceChg10,rz_balance,rz_rate,rz_buy1,rz_repay1,rz_netBuy1,rz_buy3,rz_repay3,rz_netBuy3,rz_buy5,rz_repay5,rz_netBuy5,rz_buy10,rz_repay10,rz_netBuy10,
             rq_balance, rq_number,rq_sell1, rq_repay1, rq_netSell1,rq_sell3, rq_repay3, rq_netSell3,rq_sell5, rq_repay5, rq_netSell5, rq_sell10, rq_repay10, rq_netSell10,rzrq_balance,rzrq_gap)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交
        logger.info('%s融资融券信息插入成功！',rzrq_date)


def get_rzrqDetail(page,rzrq_type,rzrq_date):
    url = 'http://dcfm.eastmoney.com/em_mutisvcexpandinterface/api/js/get?type=RZRQ_DETAIL_NJ&token=70f12f2f4f091e459a279469fe49eca5&st=rzjmre&sr=-1' \
          '&p={0}&ps=50&type=RZRQ_DETAIL_NJ&filter=(market=%27{1}%27%20and%20tdate=%27{2}T00:00:00%27)'.format(page,rzrq_type,rzrq_date)
          # r=-1表示倒序,p=n表示第几页，ps50表示每页50条数据
    headers = {'User-Agent': 'Mozilla/5.0 (Windows NT 6.1; WOW64; rv:23.0) Gecko/20100101 Firefox/23.0'}
    resp = requests.get(url, headers=headers)
    data = resp.text
    dict_data = eval(data.replace('[','').replace(']',''))
    for data in dict_data:
        #print(data)
        rzrq_scode = data["scode"]
        rzrq_sname = data["sname"]
        if data["close"] == '-':
            price_close = 0.0
        else:
            price_close = float(data["close"])

        if data["zdf"] == '-':
            price_chg = 0.0
        else:
            price_chg = round(float(data["zdf"]),2)
        rz_rzye = float(data["rzye"])
        if data["rzyezb"] == '-':
            rz_rzyezb = 0.0
        else:
            rz_rzyezb = float(data["rzyezb"])
        rz_rzmre = float(data["rzmre"])
        rz_rzche = float(data["rzche"])
        rz_rzjmre = rz_rzmre - rz_rzche

        rq_rqye = float(data["rqye"])
        rq_rqyl = float(data["rqyl"])
        rq_rqmcl = float(data["rqmcl"])
        rq_rqchl = float(data["rqchl"])
        rq_rqjmcl = rq_rqmcl - rq_rqchl

        rzrq_rzrqye = rz_rzye + rq_rqye             # 融资融券总余额（元）
        rzrq_rzrqyecz = rz_rzye - rq_rqye           # 融资融券差额（元）

        rzrqDetail_to_mysql(rzrq_type,rzrq_date,rzrq_scode,rzrq_sname,price_close,price_chg,rz_rzye,rz_rzyezb,rz_rzmre,rz_rzche,rz_rzjmre,
              rq_rqye,rq_rqyl,rq_rqmcl,rq_rqchl,rq_rqjmcl,rzrq_rzrqye,rzrq_rzrqyecz)


# 将每个股票每天融资融券信息插入到数据库
def rzrqDetail_to_mysql(rzrq_market,rzrq_date,rzrq_scode,rzrq_sname,price_close,price_chg,rz_rzye,rz_rzyezb,rz_rzmre,rz_rzche,rz_rzjmre,
              rq_rqye,rq_rqyl,rq_rqmcl,rq_rqchl,rq_rqjmcl,rzrq_rzrqye,rzrq_rzrqyecz):
    sql = "insert into stock_rzrqDetail (rzrq_market,rzrq_date,rzrq_scode,rzrq_sname,price_close,price_chg,rz_rzye,rz_rzyezb,rz_rzmre,rz_rzche,rz_rzjmre," \
          "rq_rqye,rq_rqyl,rq_rqmcl,rq_rqchl,rq_rqjmcl,rzrq_rzrqye,rzrq_rzrqyecz) values" \
          "('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');" \
          % (rzrq_market,rzrq_date,rzrq_scode,rzrq_sname,price_close,price_chg,rz_rzye,rz_rzyezb,rz_rzmre,rz_rzche,rz_rzjmre,
              rq_rqye,rq_rqyl,rq_rqmcl,rq_rqchl,rq_rqjmcl,rzrq_rzrqye,rzrq_rzrqyecz)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交
        logger.info('%s融资融券信息插入成功！',rzrq_scode)


def main(date):
    # 总融资融券
    get_rzrqInfo(1,'SZ')       # 深市
    get_rzrqInfo(1,'SH')       # 沪市

    # 具体股票
    for i in range(1,10):
        get_rzrqDetail(i, 'SZ', date)  # 深市

    for i in  range(1,12):
        get_rzrqDetail(i, 'SH', date)  # 沪市


if __name__ == '__main__':
    cTime = time.time()
    createTable()
    main('2019-03-29')
    logger.info("总共耗时：%s ", time.time() - cTime)